package com.richmond.util;

import com.richmond.data.bilan.BilanRecord;
import com.richmond.data.bilan.Category;
import com.richmond.data.bilan.CategoryRecords;
import com.richmond.data.bilan.Gl;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * User: csavard
 * Date: 12-08-24
 * Time: 09:41
 */
public class BilanParser {
    private static Logger LOGGER = Logger.getLogger(BilanParser.class);
    private static final int CODE_COL = 0;
    private static final int DESCRIPTION_COL = 1;
    private static final int JANVIER = 2;
    private static final int FEVRIER = 3;
    private static final int MARS = 4;
    private static final int AVRIL = 5;
    private static final int MAI = 6;
    private static final int JUIN = 7;
    private static final int JUILLET = 8;
    private static final int AOUT = 9;
    private static final int SEPTEMBRE = 10;
    private static final int OCTOBRE = 11;
    private static final int NOVEMBRE = 12;
    private static final int DECEMBRE = 13;
    private static final int TOTAL = 14;

    public static Map<Category,CategoryRecords> parse(InputStream in) throws IOException {
        LOGGER.info("Create Workbook");
        Workbook wb = new XSSFWorkbook(in);
        //Workbook wb  =  new HSSFWorkbook(in);
        LOGGER.info("Create Workbook done");

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

        Map<Category, CategoryRecords> bilanRecordMap = new LinkedHashMap<>();
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sheet = wb.getSheetAt(i);
            Category category = Category.getCategory(sheet.getSheetName());
            if (category != null) {
                LOGGER.info("Parse: " + sheet.getSheetName());
                int rowindex = 0;
                while (rowindex < sheet.getLastRowNum()) {
                    Row row = sheet.getRow(rowindex);
                    if (row != null) {
                        Cell cell = row.getCell(CODE_COL);
                        if (cell != null) {
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                double numericCellValue = cell.getNumericCellValue();
                                Integer value = ((Double) numericCellValue).intValue();
                                Cell descriptionCell = row.getCell(DESCRIPTION_COL);
                                if (descriptionCell != null && descriptionCell.getCellType() == Cell.CELL_TYPE_STRING) {
                                    String description = descriptionCell.getStringCellValue();
                                    if (description != null && description.length() > 0) {

                                        Collection<Integer> rowsForCode = getRowsForCode(sheet, rowindex);
                                        if (rowsForCode != null && !rowsForCode.isEmpty()) {
                                            for (Integer codeRowIndex : rowsForCode) {
                                                Row codeRow = sheet.getRow(codeRowIndex);
                                                Cell compteCell = codeRow.getCell(DESCRIPTION_COL);
                                                if (compteCell != null && compteCell.getCellType() == Cell.CELL_TYPE_STRING) {
                                                    String compteCellValue = compteCell.getStringCellValue();
                                                    Gl compte = Gl.getGl(compteCellValue);
                                                    if (compte != null) {
                                                        BilanRecord bilanRecord = new BilanRecord();
                                                        bilanRecord.setExcelRow(codeRow.getRowNum() + 1);
                                                        bilanRecord.setCode(value);
                                                        bilanRecord.setDescription(description.trim());
                                                        bilanRecord.setGl(compte);
                                                        bilanRecord.setCategory(category);
                                                        try {
                                                            bilanRecord.setJanvier(getDoubleValue(sheet, codeRow.getCell(JANVIER), evaluator));
                                                            bilanRecord.setFevrier(getDoubleValue(sheet, codeRow.getCell(FEVRIER), evaluator));
                                                            bilanRecord.setMars(getDoubleValue(sheet, codeRow.getCell(MARS), evaluator));
                                                            bilanRecord.setAvril(getDoubleValue(sheet, codeRow.getCell(AVRIL), evaluator));
                                                            bilanRecord.setMai(getDoubleValue(sheet, codeRow.getCell(MAI), evaluator));
                                                            bilanRecord.setJuin(getDoubleValue(sheet, codeRow.getCell(JUIN), evaluator));
                                                            bilanRecord.setJuillet(getDoubleValue(sheet, codeRow.getCell(JUILLET), evaluator));
                                                            bilanRecord.setAout(getDoubleValue(sheet, codeRow.getCell(AOUT), evaluator));
                                                            bilanRecord.setSeptembre(getDoubleValue(sheet, codeRow.getCell(SEPTEMBRE), evaluator));
                                                            bilanRecord.setOctobre(getDoubleValue(sheet, codeRow.getCell(OCTOBRE), evaluator));
                                                            bilanRecord.setNovembre(getDoubleValue(sheet, codeRow.getCell(NOVEMBRE), evaluator));
                                                            bilanRecord.setDecembre(getDoubleValue(sheet, codeRow.getCell(DECEMBRE), evaluator));
                                                            bilanRecord.setTotalExcel(getDoubleValue(sheet, codeRow.getCell(TOTAL), evaluator));
                                                        } catch (Exception e) {
                                                            throw new RuntimeException(sheet.getSheetName() + e.toString() + ". " + bilanRecord.toString(), e);
                                                        }

                                                        CategoryRecords categoryRecords = bilanRecordMap.get(bilanRecord.getCategory());
                                                        if(categoryRecords == null){
                                                            categoryRecords = new CategoryRecords();
                                                            categoryRecords.setCategory(category);
                                                            bilanRecordMap.put(bilanRecord.getCategory(), categoryRecords);
                                                        }
                                                        categoryRecords.add(bilanRecord);
                                                    }
                                                }
                                            }
                                        }

                                    }
                                }
                            }
                        }
                    }

                    rowindex++;
                }
            }
        }

        return bilanRecordMap;
    }

    private static double getDoubleValue(Sheet sheet, Cell cell, FormulaEvaluator evaluator) {
        double value = 0.0;
        try {
            value = ParserUtil.getDoubleValue(cell, evaluator);
        } catch (Exception e) {
            LOGGER.warn(sheet.getSheetName() + " " + e.getMessage(), e);
        }
        return value;
    }

    private static Collection<Integer> getRowsForCode(Sheet sheet, int codeRow) {
        Collection<Integer> rows = new Vector<>();
        boolean limitFound = false;

        int topLimitRow = codeRow;
        while (!limitFound) {
            Row row = sheet.getRow(topLimitRow);
            Cell cell = row.getCell(CODE_COL);
            CellStyle cellStyle = cell.getCellStyle();
            if (cellStyle.getBorderBottom() != 0) {
                limitFound = true;
                topLimitRow++;
            } else if (cellStyle.getBorderTop() != 0) {
                limitFound = true;
            } else {
                topLimitRow--;
            }
        }

        limitFound = false;
        int bottomLimitRow = codeRow;
        while (!limitFound) {
            Row row = sheet.getRow(bottomLimitRow);
            Cell cell = row.getCell(CODE_COL);
            CellStyle cellStyle = cell.getCellStyle();
            if (cellStyle.getBorderTop() != 0) {
                limitFound = true;
                bottomLimitRow--;
            } else if (cellStyle.getBorderBottom() != 0) {
                limitFound = true;
            } else {
                bottomLimitRow++;
            }
        }

        for (int i = topLimitRow; i < bottomLimitRow + 1; i++) {
            rows.add(i);
        }

        return rows;
    }
}